package com.atsmart.app.sql;

import java.util.ArrayList;
import java.util.List;
import javax.swing.text.DefaultEditorKit.CutAction;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteStatement;

import com.atsmart.app.model.ProductInfo;
import com.atsmart.app.model.ProductListInfo;

public class ProductSqlHelper {

	private SQLiteDatabase db = null;

	/**
	 * 打开数据库
	 */
	public void open(SqlHelper dh) {
		// dh = new SqlHelper(null, "atsmart", null,SqlHelper.getDBVersion());
		db = dh.getWritableDatabase();
	}

	public synchronized void addProductInfo(ProductInfo at,int gid) {
		if (at == null) {
			return;
		}
		String strSql = "insert or replace into product_info (gid,name,product_id,description,logo) values(?,?,?,?,?);";
		try {
			SQLiteStatement stm = db.compileStatement(strSql);
			stm.bindLong(1,gid);
			stm.bindString(2, at.getName()==null?"":at.getName());
			stm.bindLong(3, at.getProduct_id());
			stm.bindString(4, at.getDescription()==null?"":at.getDescription());
			stm.bindString(5, at.getLogo()==null?"":at.getLogo());
			stm.execute();
			stm.clearBindings();
			stm.close();
		} catch (Exception e) {
			e.printStackTrace();
			// will not happen
		}
	}

	public ProductListInfo getProductInfo(int begin, int pagesize,String keyword,int gid) {
		String sql ="";
		if(keyword!=null&&!"".equals(keyword)){
			sql = " select _id,gid,name,product_id,description,logo from product_info where name like '%"+keyword+"%'  limit "
					+ begin + "," + pagesize;
		}else{
			sql = " select _id,gid,name,product_id,description,logo from product_info where gid="+gid+" limit "
					+ begin + "," + pagesize;
		}
		 
		Cursor cursor = db.rawQuery(sql, null);
		ProductInfo historie = null;
		List<ProductInfo> nums = null;
		ProductListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ProductInfo>();
				t = new ProductListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ProductInfo();
					historie.setName(cursor.getString(2));
					historie.setProduct_id(cursor.getInt(3));
					historie.setDescription(cursor.getString(4));
					historie.setLogo(cursor.getString(5));
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setProducts(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	public ProductListInfo getProductInfoList(String gids) {
		String sql = " select _id,gid,name,product_id,description,logo from product_info where gid in("+gids+")";
		 
		Cursor cursor = db.rawQuery(sql, null);
		ProductInfo historie = null;
		List<ProductInfo> nums = null;
		ProductListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ProductInfo>();
				t = new ProductListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ProductInfo();
					historie.setName(cursor.getString(2));
					historie.setProduct_id(cursor.getInt(3));
					historie.setDescription(cursor.getString(4));
					historie.setLogo(cursor.getString(5));
			
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setProducts(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	
	public ProductListInfo getProduct(int product_id){
		String sql = " select _id,gid,name,product_id,description,logo from product_info where product_id=" + product_id;
		 
		Cursor cursor = db.rawQuery(sql, null);
		ProductInfo historie = null;
		List<ProductInfo> nums = null;
		ProductListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ProductInfo>();
				t = new ProductListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ProductInfo();
					historie.setName(cursor.getString(2));
					historie.setProduct_id(cursor.getInt(3));
					historie.setDescription(cursor.getString(4));
					historie.setLogo(cursor.getString(5));
				
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setProducts(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	
	public ProductListInfo getProduct(){
		String sql = " select _id,gid,name,product_id,description,logo from product_info";
		 
		Cursor cursor = db.rawQuery(sql, null);
		ProductInfo historie = null;
		List<ProductInfo> nums = null;
		ProductListInfo t = null;
		try {
			if (cursor != null && cursor.moveToFirst()) {
				nums = new ArrayList<ProductInfo>();
				t = new ProductListInfo();
				for (int i = 0; i < cursor.getCount(); i++) {
					historie = new ProductInfo();
					historie.setName(cursor.getString(2));
					historie.setProduct_id(cursor.getInt(3));
					historie.setDescription(cursor.getString(4));
					historie.setLogo(cursor.getString(5));
				
					nums.add(historie);
					cursor.moveToNext();
				}
				t.setProducts(nums);
			}
		} catch (Exception e) {
			return null;
		} finally {
			cursor.close();
		}
		return t;
	}
	
	
	public int getCount() {
		String sql = " select _id,gid,name,product_id,description,logo from product_info ";
		Cursor cursor = db.rawQuery(sql, null);
		try {
			if (cursor != null && cursor.moveToFirst()) {
				return cursor.getCount();
			}
		} catch (Exception e) {
			return 0;
		} finally {
			cursor.close();
		}
		return 0;
	}

	
	public void deleteAttribute(int id) {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from product_info where product_id="+id);
	}
	public void deleteProItem() {
		// db.delete("position_attribute", null, null);
		db.execSQL("delete from product_info ");
	}
}
